/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: zhaosj
 * Date: 2010-4-21 上午11:45:14
 */
package com.jxl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @author zhaosj
 * 
 */
public class CcxDataHandle {

	//预读取的EXCEL文档路径
	static String fileSource = "D:\\Book1.xls" ;
	
	//预读取的EXCEL文档SHEET名称
	static String sheetName = "Sheet1" ;
	
	//计算结果保存的EXCEL文档
	static String fileTarget = "D:\\Book2.xls" ;
	
	public static void main(String[] args) {

		readExcel(fileSource);
		
		System.out.println("complate...");
		System.exit(0) ;

	}

	//文件操作
	public static void readExcel(String filePath) {
		
			InputStream is = null ;
			Workbook rwb1 = null ;
			Sheet st = null ;
			
			WritableWorkbook wwb = null ;
			WritableSheet ws = null ;

		try {	
			is = new FileInputStream(filePath);
			rwb1 = Workbook.getWorkbook(is);
			st = rwb1.getSheet(sheetName);
			
			wwb = Workbook.createWorkbook(new File(fileTarget));
			ws = wwb.createSheet("Sheet1", 0);
			
			int rows = st.getRows();	//行数
			int cols = st.getColumns();	//列数
			
			for (int i = 0; i <= rows; i++)
				ws.setColumnView(i, 10);
			
			Cell cell = null ;	//单元格
			jxl.write.Number number = null ;	//数字类型
			Label date = null ;	//日期类型
			Label label = null ;	//字符类型
			
			//添加第一行样式
			jxl.write.WritableFont wf = new jxl.write.WritableFont(
					WritableFont.ARIAL, 10, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setAlignment(Alignment.CENTRE); 
			wcf.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW); 
			
			for (int i = 0; i < cols; i++) {
				
				cell = st.getCell(i , 0) ;
				label = new Label(0, i, cell.getContents());
				ws.addCell(label) ;
				
				for (int j = rows -1 ; j > 0; j--) {

					//不读取第二行
					if (j == 1)
						continue ;
					
					cell = st.getCell(i , j) ;
					
					if (cell.getType() == CellType.NUMBER)
					{
						number = new jxl.write.Number(rows - j, i, Double.parseDouble(cell.getContents())) ;
						ws.addCell(number) ;
					}else if (cell.getType() == CellType.DATE)
					{
						date = new Label(rows - j,i,cell.getContents(), wcf) ;
						ws.addCell(date) ;
					}else if (cell.getType() == CellType.LABEL)
					{
						label = new Label(rows - j,i,cell.getContents()) ;
						ws.addCell(label) ;
					}
				}
			}
			
			//wwb.write();
			
			operateValue(wwb) ;
			
			calculate(wwb) ;
			
			rwb1.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			if (rwb1 != null)
				rwb1.close() ;
			if (wwb != null)
				try {
					wwb.close() ;
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}
	
	//填充空值
	public static void operateValue(WritableWorkbook wwb){
		
		try {
			WritableSheet st = wwb.getSheet(0);
			
			int rows = st.getRows(); //行数
			int cols = st.getColumns(); //列数
			
			Cell cell = null;
			int colNull = 0;
			String str = null;
			jxl.write.Number number = null ;	//数字类型
			
			for (int row = 1; row < rows; row++) {
				for (int col = 1; col < cols; col++) {
					cell = st.getCell(col, row);
					str = cell.getContents();

					if (str == null || str.isEmpty()) {	//发现空值

						colNull = col;
						if (col == 1) //行头为空
						{
							for (; colNull < cols; colNull++) {
								cell = st.getCell(colNull, row);
								str = cell.getContents();
								if (str != null && !str.isEmpty())
									break;
							}
							for (; col < colNull; col++)
							{
								number = new jxl.write.Number(col, row, Double.parseDouble(str)) ;
								st.addCell(number) ;
							}
						}else	//中间或者行末有空值
						{
							cell = st.getCell(colNull-1 , row) ;
							String str0 = cell.getContents() ;
							for (; colNull < cols ; colNull++)	//向后查找非空值
							{
								cell = st.getCell(colNull,row) ;
								str = cell.getContents() ;
								if (str != null && !str.isEmpty())
									break ;
							}
							if (colNull == cols)	//至行末全是空值
							{
								for (; col < cols ; col++)
								{
									number = new jxl.write.Number(col, row, Double.parseDouble(str0)) ;
									st.addCell(number) ;
								}
								
							}else	//两数字之间有空值
							{
								double grad = (Double.parseDouble(str) - Double.parseDouble(str0))/(colNull - col + 1) ;
								for (; col < colNull ; col++)
								{
									cell = st.getCell(col - 1 , row) ;
									str = cell.getContents() ;
									number = new jxl.write.Number(col, row, Double.parseDouble(str) + grad) ;
									st.addCell(number) ;
								}
							}
						}
					}
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace() ;
		}
	}
	
	//calculate
	public static void calculate(WritableWorkbook wwb)
	{
		try {
			WritableSheet st = wwb.getSheet(0);
			WritableSheet ws = wwb.createSheet("Sheet2" , 1) ;
			
			int rows = st.getRows(); //行数
			int cols = st.getColumns(); //列数
			
			for (int i = 0; i <= cols; i++)
				ws.setColumnView(i, 15);
			
			Cell cell = null;
			double prev = 1 ;
			double next = 1 ;
			double curr = 0 ;
			jxl.write.Number number = null ;
			Label label = null ;
			
			//添加第一行样式
			jxl.write.WritableFont wf = new jxl.write.WritableFont(
					WritableFont.ARIAL, 10, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setAlignment(Alignment.CENTRE); 
			wcf.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW); 
			
			for (int row = 1 ; row <= rows ; row++)
			{
				//插入第0列
				cell = st.getCell(0 , row-1) ;
				label = new Label(0, row-1, cell.getContents());
				ws.addCell(label) ;
				if(row < rows)
				{
					for (int col = 2 ; col < cols ; col++)
					{
						//插入第0行
						cell = st.getCell(col-1 , 0) ;
						label = new Label(col-1, 0, cell.getContents(), wcf);
						ws.addCell(label) ;
						
						//计算
						cell = st.getCell(col-1 , row) ;
						prev = Double.parseDouble(cell.getContents()) ;
						cell = st.getCell(col , row) ;
						next = Double.parseDouble(cell.getContents()) ;
						curr = (next - prev) / (prev==0?1:prev) ;
						
						number = new jxl.write.Number(col-1, row, curr) ;
						ws.addCell(number) ;
					}
				}
				
			}
			
			//写入EXCEL
			wwb.write();
			
		} catch (Exception e) {
			e.printStackTrace() ;
		}
	}

}
